# encoding: utf-8

# sql导出
# mysqldump -u root -p db_name > test_db.sql

import pymssql
from widget.db_init.configuration import sql_configuration as LM


class SqlServerOp(object):
    '''
    '192.168.0.229': {
        'db_name': ['dspt', ],
        'charset': 'utf8',
        'sql_mode': 'PIPES_AS_CONCAT',
        'use_unicode': True,
        'port': 4706,
        'user': 'ddcollect',
        'passwd': 'Cx_123456'
    }
    '''
    def __init__(self, host, port, db_name, user, passwd, charset='utf8'):
        self.host = host
        self.port = port
        self.db_name = db_name
        self.user = user
        self.passwd = passwd
        self.charset = charset

    def operate(self, sql):
        db = pymssql.connect(server=self.host, port=self.port, user=self.user, password=self.passwd, database=self.db_name, charset=self.charset)
        cur = db.cursor()
        try:
            # 执行sql语句
            # sql = sql.encode("utf-8")
            # print(sql)
            cur.execute(sql)
            cur.close()
            # 提交到数据库执行
            db.commit()
        except Exception as e:
            print(e)
            cur.close()
            # Rollback in case there is any error
            db.rollback()
        # 关闭数据库连接
        db.close()

    def select(self, sql):
        db = pymssql.connect(server=self.host, port=self.port, user=self.user, password=self.passwd, database=self.db_name, charset=self.charset)
        cur = db.cursor()
        results = None
        try:
            # 执行sql语句
            # sql = sql.encode("utf-8", "ignore")
            # sql = sql.decode("utf-8", "ignore")
            # sql = sql.encode("GBK", "ignore")
            # print(sql)
            cur.execute(sql)
            # 获取所有记录列表
            results = cur.fetchall()
            # print(results.encode("utf-8", "ignore"))

        except Exception as e:
            print(e)
            # 关闭数据库连接
        db.close()
        return results


if __name__ == '__main__':
    # sop = SqlServerOp(host="192.168.0.6", port=1433, user='sa', passwd='cxstar2014', db_name='standarddb', charset='utf8')
    sop = SqlServerOp(host="192.168.0.193", port='1433', user='sa', passwd='cxstar@2014', db_name='cjg2cxstar',charset='utf8')

    # msg = sop.select("""SELECT * FROM [dbo].[standard] where A107='0007'""")
    # 查询当前数据库中的表名
    table_name_list = sop.select("""select name from sys.tables""")
    # table_name_list = sop.select("""select name from SYSOBJECTS  WHERE XTYPE='V'""")
    for table_name in table_name_list:
        table_name = table_name[0]
        print(table_name)
        # # 查询当前表下的所有数据
        # all_date = sop.select("""SELECT top 10 * FROM standard""")
        # for data in all_date:
        #     print(data)
        # print(all_date)
        # # 查询当前表下的字段名
        # field_name_list = sop.select("""select COLUMN_NAME from information_schema.COLUMNS where table_name = '{0}'""".format(table_name))
        # for field_name in field_name_list:
        #     field_name = field_name[0]
        #     print(field_name)
        # print('-'*40)
